/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model;

import entity.Vacancy;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import utility.DatabaseConnection;

/**
 *
 * @author SonLT
 */
public class VacancyModel {

    private String sql;
    private Connection con = null;
    private CallableStatement cs = null;
    private ResultSet rs = null;
    private PreparedStatement ps = null;

    //Get all vacancy.
    public ArrayList<Vacancy> getallVacancy() {
        try {
            con = DatabaseConnection.getConnection();
            sql = "{call sp_getAllVacancy}";
            ArrayList<Vacancy> listVan = new ArrayList<Vacancy>();
            cs = con.prepareCall(sql);
            rs = cs.executeQuery();
            while (rs.next()) {
                Vacancy vs = new Vacancy();
                vs.setVacancyID(rs.getString(1));
                vs.setDepartmentID(rs.getString(2));
                vs.setVacancyTitle(rs.getString(3));
                vs.setNumOfJob(rs.getInt(4));
                vs.setSalary(rs.getString(5));
                vs.setExperience(rs.getString(6));
                vs.setStartDate(rs.getString(7));
                vs.setCloseDate(rs.getString(8));
                vs.setStatus(rs.getString(9));
                vs.setPlace(rs.getString(10));
                listVan.add(vs);
            }
            rs.close();
            return listVan;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    //Get departmentName to fill data.
    public ArrayList<Vacancy> getdepart() {
        try {
            con = DatabaseConnection.getConnection();
            sql = "{call sp_getDPtoUp}";
            cs = con.prepareCall(sql);
            rs = cs.executeQuery();
            ArrayList<Vacancy> listVan = new ArrayList<Vacancy>();
            while (rs.next()) {
                Vacancy v = new Vacancy();
                v.setDepartmentID(rs.getString(1));
                listVan.add(v);
                System.out.println(v.toString());
            }
            rs.close();
            return listVan;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    //Insert a new vacancy.
    public boolean addVacancy(Vacancy v) {
        try {
            sql = "{call sp_addNewVacancy(?,?,?,?,?,?,?,?,?,?)}";
            con = DatabaseConnection.getConnection();
            cs = con.prepareCall(sql);
            cs.setString(1, v.getVacancyID());
            cs.setString(2, v.getDepartmentID());
            cs.setString(3, v.getVacancyTitle());
            cs.setInt(4, v.getNumOfJob());
            cs.setString(5, v.getSalary());
            cs.setString(6, v.getExperience());
            cs.setString(7, v.getStartDate());
            cs.setString(8, v.getCloseDate());
            cs.setString(9, v.getStatus());
            cs.setString(10, v.getPlace());
            int result = cs.executeUpdate();
            if (result > 0) {
                return true;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return false;
    }

    //Get one vacancy to update.
    public Vacancy getOneVacancy(String id) {
        Vacancy v = null;
        try {
            con = DatabaseConnection.getConnection();
            sql = "{call getOneVacancy(?)}";
            cs = con.prepareCall(sql);
            cs.setString(1, id);
            rs = cs.executeQuery();

            while (rs.next()) {
                v = new Vacancy();
                v.setVacancyID(rs.getString(1));
                v.setDepartmentID(rs.getString(2));
                v.setVacancyTitle(rs.getString(3));
                v.setNumOfJob(Integer.parseInt(rs.getString(4)));
                v.setSalary(rs.getString(5));
                v.setExperience(rs.getString(6));
                v.setStartDate(rs.getString(7));
                v.setCloseDate(rs.getString(8));
                v.setStatus(rs.getString(9));
                v.setPlace(rs.getString(10));
            }

            return v;

        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;
    }

    //Update a vacancy.
    public boolean updateVacancy(Vacancy v) {
        try {
            sql = "UPDATE Vacancy SET DepartmentID = ?, VacancyTitle = ?, NumOfJob = ?, Salary = ?, Experience = ?, StartDate = ?, CloseDate = ?, Status = ?, Place = ? WHERE VacancyID = ?";
            con = DatabaseConnection.getConnection();
            ps = con.prepareStatement(sql);
            ps.setString(1, v.getDepartmentID());
            ps.setString(2, v.getVacancyTitle());
            ps.setInt(3, v.getNumOfJob());
            ps.setString(4, v.getSalary());
            ps.setString(5, v.getExperience());
            ps.setString(6, v.getStartDate());
            ps.setString(7, v.getCloseDate());
            ps.setString(8, v.getStatus());
            ps.setString(9, v.getPlace());
            ps.setString(10, v.getVacancyID());

            int result = ps.executeUpdate();

            if (result > 0) {
                return true;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return false;
    }

    //Delete a vacancy
    public boolean deleteVacancy(String vacancyID) {
        try {
            sql = "{call sp_deleteVacancy(?)}";
            con = DatabaseConnection.getConnection();
            cs = con.prepareCall(sql);
            cs.setString(1, vacancyID);
            int result = cs.executeUpdate();
            cs.close();
            con.close();
            if (result > 0) {
                return true;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return false;
    }

    //Fill by VacancyTitle
    public ArrayList<Vacancy> getVacancyByTitle(String vacancyTitle) {
        try {
            con = DatabaseConnection.getConnection();
            sql = "{call sp_getVacancyByTitle(?)}";
            ArrayList<Vacancy> listVan = new ArrayList<Vacancy>();
            cs = con.prepareCall(sql);
            cs.setString(1, vacancyTitle);
            rs = cs.executeQuery();
            while (rs.next()) {
                Vacancy vs = new Vacancy();
                vs.setVacancyID(rs.getString(1));
                vs.setDepartmentID(rs.getString(2));
                vs.setVacancyTitle(rs.getString(3));
                vs.setNumOfJob(rs.getInt(4));
                vs.setSalary(rs.getString(5));
                vs.setExperience(rs.getString(6));
                vs.setStartDate(rs.getString(7));
                vs.setCloseDate(rs.getString(8));
                vs.setStatus(rs.getString(9));
                vs.setPlace(rs.getString(10));
                listVan.add(vs);
            }
            rs.close();
            return listVan;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public ArrayList<Vacancy> getTop5Vacancy() {
        try {
            con = DatabaseConnection.getConnection();
            ArrayList<Vacancy> listVan = new ArrayList<Vacancy>();
            String sql = "select top 5 * from Vacancy order by StartDate desc";
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Vacancy vs = new Vacancy();
                vs.setVacancyID(rs.getString(1));
                vs.setDepartmentID(rs.getString(2));
                vs.setVacancyTitle(rs.getString(3));
                vs.setNumOfJob(rs.getInt(4));
                vs.setSalary(rs.getString(5));
                vs.setExperience(rs.getString(6));
                vs.setStartDate(rs.getString(7));
                vs.setCloseDate(rs.getString(8));
                vs.setStatus(rs.getString(9));
                vs.setPlace(rs.getString(10));
                listVan.add(vs);
            }
            rs.close();
            return listVan;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public ArrayList<Vacancy> getOpenVacancy() {
        try {
            con = DatabaseConnection.getConnection();
            ArrayList<Vacancy> listVan = new ArrayList<Vacancy>();
            sql = "select * from Vacancy where Status = 'Open'";
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Vacancy vs = new Vacancy();
                vs.setVacancyID(rs.getString(1));
                vs.setDepartmentID(rs.getString(2));
                vs.setVacancyTitle(rs.getString(3));
                vs.setNumOfJob(rs.getInt(4));
                vs.setSalary(rs.getString(5));
                vs.setExperience(rs.getString(6));
                vs.setStartDate(rs.getString(7));
                vs.setCloseDate(rs.getString(8));
                vs.setStatus(rs.getString(9));
                vs.setPlace(rs.getString(10));
                listVan.add(vs);
            }
            rs.close();
            return listVan;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public ArrayList<Vacancy> getApplyVacancy(String aid) {
        try {
            con = DatabaseConnection.getConnection();
            ArrayList<Vacancy> listVan = new ArrayList<Vacancy>();
            sql = "select v.VacancyTitle, v.Salary, v.CloseDate, i.Status "
                    + "from Vacancy v inner join Interview i "
                    + "on v.VacancyID = i.VacancyID "
                    + "where i.ApplicantID = ?";
            ps = con.prepareStatement(sql);
            ps.setString(1, aid);
            rs = ps.executeQuery();
            while (rs.next()) {
                Vacancy vs = new Vacancy();
                vs.setVacancyTitle(rs.getString(1));
                vs.setSalary(rs.getString(2));
                vs.setCloseDate(rs.getString(3));
                vs.setStatus(rs.getString(4));
                listVan.add(vs);
            }
            rs.close();
            return listVan;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public ArrayList<Vacancy> getVacancyByDepartment(String id) {
        try {
            con = DatabaseConnection.getConnection();
            ArrayList<Vacancy> listVan = new ArrayList<Vacancy>();
            sql = "select * from Vacancy where Status = 'Open' and DepartmentID=?";
            ps = con.prepareStatement(sql);
            ps.setString(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                Vacancy vs = new Vacancy();
                vs.setVacancyID(rs.getString(1));
                vs.setDepartmentID(rs.getString(2));
                vs.setVacancyTitle(rs.getString(3));
                vs.setNumOfJob(rs.getInt(4));
                vs.setSalary(rs.getString(5));
                vs.setExperience(rs.getString(6));
                vs.setStartDate(rs.getString(7));
                vs.setCloseDate(rs.getString(8));
                vs.setStatus(rs.getString(9));
                vs.setPlace(rs.getString(10));
                listVan.add(vs);
            }
            rs.close();
            return listVan;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public ArrayList<Vacancy> getdistinctVacancy() {
        try {
            con = DatabaseConnection.getConnection();
            sql = "select distinct Salary from Vacancy ";
            ArrayList<Vacancy> listVan = new ArrayList<Vacancy>();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Vacancy v = new Vacancy();
                v.setSalary(rs.getString(1));
                listVan.add(v);
            }

            return listVan;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public ArrayList<Vacancy> getdistinctYear() {
        try {
            con = DatabaseConnection.getConnection();
            sql = "select distinct Experience from Vacancy ";
            ArrayList<Vacancy> listVan = new ArrayList<Vacancy>();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Vacancy v = new Vacancy();
                v.setExperience(rs.getString(1));
                listVan.add(v);
            }

            return listVan;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public ArrayList<Vacancy> getVacancyBySalary(String id) {
        try {
            con = DatabaseConnection.getConnection();
            ArrayList<Vacancy> listVan = new ArrayList<Vacancy>();
            sql = "select * from Vacancy where Status = 'Open' and Salary=?";
            ps = con.prepareStatement(sql);
            ps.setString(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                Vacancy vs = new Vacancy();
                vs.setVacancyID(rs.getString(1));
                vs.setDepartmentID(rs.getString(2));
                vs.setVacancyTitle(rs.getString(3));
                vs.setNumOfJob(rs.getInt(4));
                vs.setSalary(rs.getString(5));
                vs.setExperience(rs.getString(6));
                vs.setStartDate(rs.getString(7));
                vs.setCloseDate(rs.getString(8));
                vs.setStatus(rs.getString(9));
                vs.setPlace(rs.getString(10));
                listVan.add(vs);
            }
            rs.close();
            return listVan;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public ArrayList<Vacancy> getVacancyByYear(String id) {
        try {
            con = DatabaseConnection.getConnection();
            ArrayList<Vacancy> listVan = new ArrayList<Vacancy>();
            sql = "select * from Vacancy where Status = 'Open' and Experience=?";
            ps = con.prepareStatement(sql);
            ps.setString(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                Vacancy vs = new Vacancy();
                vs.setVacancyID(rs.getString(1));
                vs.setDepartmentID(rs.getString(2));
                vs.setVacancyTitle(rs.getString(3));
                vs.setNumOfJob(rs.getInt(4));
                vs.setSalary(rs.getString(5));
                vs.setExperience(rs.getString(6));
                vs.setStartDate(rs.getString(7));
                vs.setCloseDate(rs.getString(8));
                vs.setStatus(rs.getString(9));
                vs.setPlace(rs.getString(10));
                listVan.add(vs);
            }
            rs.close();
            return listVan;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public ArrayList<Vacancy> getVacancyByName(String id) {
        try {
            con = DatabaseConnection.getConnection();
            ArrayList<Vacancy> listVan = new ArrayList<Vacancy>();
            sql = "select * from Vacancy where Status = 'Open' and VacancyTitle like ?";
            ps = con.prepareStatement(sql);
            ps.setString(1, "%" + id + "%");
            rs = ps.executeQuery();
            while (rs.next()) {
                Vacancy vs = new Vacancy();
                vs.setVacancyID(rs.getString(1));
                vs.setDepartmentID(rs.getString(2));
                vs.setVacancyTitle(rs.getString(3));
                vs.setNumOfJob(rs.getInt(4));
                vs.setSalary(rs.getString(5));
                vs.setExperience(rs.getString(6));
                vs.setStartDate(rs.getString(7));
                vs.setCloseDate(rs.getString(8));
                vs.setStatus(rs.getString(9));
                vs.setPlace(rs.getString(10));
                listVan.add(vs);
            }
            rs.close();
            return listVan;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public int getNumofjob(String vid){
        int num = 0;
        try {
            con = DatabaseConnection.getConnection();
            sql = "select NumOfJob from Vacancy where VacancyID = ?";
            ps = con.prepareStatement(sql);
            ps.setString(1, vid);
            rs = ps.executeQuery();
            while (rs.next()) {
                num = Integer.parseInt(rs.getString(1));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return num;
    }

    public boolean updateStatusVacancy(String vid, String status) {
        try {
            sql = "UPDATE Vacancy SET Status = ? WHERE VacancyID = ?";
            con = DatabaseConnection.getConnection();
            ps = con.prepareStatement(sql);
            ps.setString(1, status);
            ps.setString(2, vid);

            int result = ps.executeUpdate();
            if (result > 0) {
                return true;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return false;
    }
}
